package com.citywithincity.db.drivers.oracle;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.alibaba.druid.pool.DruidPooledPreparedStatement;
import com.citywithincity.db.AbsSqlDriver;
import com.citywithincity.db.ColumnInfo;
import com.citywithincity.db.DBException;
import com.citywithincity.db.DbUtil;
import com.citywithincity.db.Entity;
import com.citywithincity.db.EntityField;
import com.citywithincity.db.SqlHelper;
import com.citywithincity.db.interfaces.IColumnDataValidate;
import com.citywithincity.db.interfaces.IValueConvert;
import com.citywithincity.db.interfaces.Select;
import com.citywithincity.db.validate.oracle.NVarchar2Validate;
import com.citywithincity.db.validate.oracle.Varchar2Validate;

import oracle.jdbc.driver.OraclePreparedStatement;

public class OracleDriver extends AbsSqlDriver {

	public OracleDriver() {
	}

	
	private Object getAutoValue(Connection connection,Object entity, EntityField[] uniqueFields,String tableName,String autoColumnMame){
		StringBuilder sb = new StringBuilder(50);
		sb.append("SELECT ").append(autoColumnMame).append("  FROM ")
			.append(tableName).append(" WHERE ");
		boolean first = true;
		for(int i=0 ,c = uniqueFields.length; i < c ; ++i){
			if(first){
				first = false;
			}else{
				sb.append(',');
			}
			sb.append(uniqueFields[i].getColumnName()).append("=?");
		}
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = connection.prepareStatement(sb.toString());
			int index = 1;
			for(int i=0 ,c = uniqueFields.length; i < c ; ++i){
				uniqueFields[i].setPs(ps, index++, entity);
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getObject(1);
			}
			return null;
		} catch (Exception e) {
			
			throw new DBException(e);
		} finally {
			DbUtil.close(rs, ps);
		}
	}
	
	@Override
	public int insert(Connection connection, Object data, Entity<?> entity) throws SQLException {

		final EntityField[] fields = entity.getFields();
		final EntityField[] uniqueFields = entity.getUniqueFields();
		final String tableName = entity.getTableName();
		List<Object> insertValues = new ArrayList<Object>(fields.length);
		List<EntityField> insertFields = new ArrayList<EntityField>(fields.length);

		if (entity.isAuto()) {
			if(uniqueFields != null){
				int ret = insert(connection, insertFields, insertValues, tableName, fields, uniqueFields, data);
				//加载
				Object value = getAutoValue(connection, data, uniqueFields, tableName ,entity.getAutoColumnName() );
				
				entity.getPks()[0].set(data, value);
				
				//entity.setAutoGenerate(entity, value);
				return ret;
			}else{
				PreparedStatement ps = null;
				ResultSet rs = null;
				try {
					StringBuilder sb = SqlHelper.makeInsertReturning(insertFields, insertValues, tableName, fields,
							uniqueFields, data, entity.getAutoColumnName());
					String sql = sb.toString();
					ps = connection.prepareStatement(sql);
					DruidPooledPreparedStatement dps = (DruidPooledPreparedStatement) ps;
					OraclePreparedStatement oraclePs = (OraclePreparedStatement) dps.getRawPreparedStatement();
					int result = SqlHelper.executeInsert(data, oraclePs, sql, uniqueFields, insertFields, insertValues,true);
					if (result > 0) {
						rs = oraclePs.getReturnResultSet();
						rs.next();
						Object object = rs.getLong(1);
						entity.setAutoGenerate(data, object);
					}
					return result;
				} finally {
					DbUtil.close(rs, ps);
				}
			}
			
		} else {
			return insert(connection, insertFields, insertValues, tableName, fields, uniqueFields, data);
		}

	}
	
	
	private int insert( Connection connection, List<EntityField> insertFields, List<Object> insertValues, String tableName, EntityField[] fields, EntityField[] uniqueFields, Object data) throws SQLException{
		StringBuilder sb = SqlHelper.makeOracleInsert(insertFields,
				insertValues, 
				tableName,
				fields, 
				uniqueFields, 
				data);
		PreparedStatement ps = null;
		String sql = sb.toString();
		try {
			ps = connection.prepareStatement(sql);
			return SqlHelper.executeInsert(ps, sql, data,uniqueFields, insertFields, insertValues);
		} catch (SQLException e) {
			// 记录下
			sql = SqlHelper.replaceStatement(data, sql, uniqueFields, insertFields, insertValues);
			logger.error(sql);
			throw e;
		} finally {
			DbUtil.close(ps);
		}
	}

	public void buildPage(StringBuilder sb, Integer position, Integer pageSize) {
		sb.insert(0, "SELECT * FROM(SELECT A.*, rownum r FROM (").append(") A WHERE rownum <= ")
				.append(position + pageSize).append(" ) B WHERE r > ").append(position);
	}

	public int posToPage(Integer position, Integer pageSize) {
		++position;
		if (position % pageSize == 0) {
			return position / pageSize;
		}
		return position / pageSize + 1;
	}

	@Override
	public String protectTableName(String tableName) {
		return tableName.toUpperCase();
	}

	@Override
	public PreparedStatement prepareInsert(Connection connection, String sql, String pk) throws SQLException {
		return connection.prepareStatement(sql, new String[] { pk });
	}

	@Override
	public Object getAutoIncrease(PreparedStatement ps) throws SQLException {
		ResultSet rs = null;
		try {
			rs = ps.getGeneratedKeys();
			if (rs.next()) {
				return rs.getString(1);
			} else {
				return 0;
			}
		} finally {
			DbUtil.close(rs);
		}
	}

	@Override
	public IColumnDataValidate createValidate(ColumnInfo columnInfo, ResultSetMetaData data) throws SQLException {
		if (columnInfo.type == Types.VARCHAR) {
			return new Varchar2Validate(data.getPrecision(columnInfo.index), data.isNullable(columnInfo.index));
		} else if (columnInfo.type == Types.NVARCHAR) {
			return new NVarchar2Validate(data.getPrecision(columnInfo.index), data.isNullable(columnInfo.index));
		}
		return null;
	}

	@Override
	public Class<?> getAutoIncreaseClass() {
		return Long.class;
	}

	private static Log logger = LogFactory.getLog(OracleDriver.class);

	@Override
	public Class<?> getColumnClass(ResultSetMetaData data, int i) {

		return null;
	}

	private static final char BLANK = ' ';
	
	@Override
	public int insertOrUpdate(Select<?> ar, String tableName, String keyName, Map<String, Object> map) {
		StringBuilder sb = new StringBuilder();
		sb.append("MERGE INTO").append(BLANK).append(tableName).append(" T1 ")
		.append("USING (SELECT ");

		List<Object> values = new ArrayList<Object>();
		
		boolean first = true;
		for (Entry<String, Object> entry  : map.entrySet()) {
			String key = entry.getKey();
			
			
			if(first){
				first = false;
			}else{
				sb.append(',');
			}
			sb.append("? AS ").append(key);
			values.add(entry.getValue());
		}
		
		sb.append(" FROM DUAL) T2 ON ( T1.").append(keyName).append("=T2.").append(keyName)
		.append(") ").append("WHEN MATCHED THEN UPDATE SET ");
		
		
		first = true;
		for (Entry<String, Object> entry  : map.entrySet()) {
			String key = entry.getKey();
			if(key.equals(keyName)){
				continue;
			}
			
			if(first){
				first = false;
			}else{
				sb.append(',');
			}
			sb.append("T1.").append(key).append("=?");
			values.add(entry.getValue());
		}
		
		
		sb.append(" WHEN NOT MATCHED THEN INSERT (");
		
		first = true;
		for (Entry<String, Object> entry  : map.entrySet()) {
			String key = entry.getKey();
			
			if(first){
				first = false;
			}else{
				sb.append(',');
			}
			sb.append("T1.").append(key);
			values.add(entry.getValue());
		}
		
		sb.append(") VALUES (");
		
		first = true;
		for (int i =0 ,c =map.size(); i < c; ++i) {
			if(first){
				first = false;
			}else{
				sb.append(",");
			}
			sb.append("?");
		}
		
		sb.append(")");
		return ar.executeUpdate(sb.toString(), values.toArray(new Object[values.size()]) );
	}

	@Override
	public int insertOrUpdate(Select<?> ar, String tableName, String[] unikeys, Map<String, Object> map) {
		StringBuilder sb = new StringBuilder();
		sb.append("MERGE INTO").append(BLANK).append(tableName).append(" T1 ")
		.append("USING (SELECT ");

		List<Object> values = new ArrayList<Object>();
		
		boolean first = true;
		for (Entry<String, Object> entry  : map.entrySet()) {
			String key = entry.getKey();
			
			
			if(first){
				first = false;
			}else{
				sb.append(',');
			}
			sb.append("? AS ").append(key);
			values.add(entry.getValue());
		}
		
		Set<String> keys = new HashSet<String>();
		sb.append(" FROM DUAL) T2 ON ( ");
		
		first = true;
		for (String key : unikeys) {
			
			if(first){
				first = false;
			}else{
				sb.append(" AND ");
			}
			
			sb.append("T1.").append(key).append("=").append("T2.").append(key);
			keys.add(key);
		}
		
		
		sb.append(") ").append("WHEN MATCHED THEN UPDATE SET ");
		
		
		first = true;
		for (Entry<String, Object> entry  : map.entrySet()) {
			String key = entry.getKey();
			if(keys.contains(key)){
				continue;
			}
			
			if(first){
				first = false;
			}else{
				sb.append(',');
			}
			sb.append("T1.").append(key).append("=?");
			values.add(entry.getValue());
		}
		
		
		sb.append(" WHEN NOT MATCHED THEN INSERT (");
		
		first = true;
		for (Entry<String, Object> entry  : map.entrySet()) {
			String key = entry.getKey();
			
			if(first){
				first = false;
			}else{
				sb.append(',');
			}
			sb.append("T1.").append(key);
			values.add(entry.getValue());
		}
		
		sb.append(") VALUES (");
		
		first = true;
		for (int i =0 ,c =map.size(); i < c; ++i) {
			if(first){
				first = false;
			}else{
				sb.append(",");
			}
			sb.append("?");
		}
		
		sb.append(")");
		return ar.executeUpdate(sb.toString(), values.toArray(new Object[values.size()]) );
	}


	@Override
	public IValueConvert create(Class<?> columnClass, Class<?> targetClass) {
		//blob,clob
		
		return null;
	}


	@Override
	public String protectColumn(String name) {
		// TODO Auto-generated method stub
		return name;
	}

	
}
